from collections import OrderedDict
from datetime import datetime, timedelta

from django.db.models import Sum, Count, F
from django.http import JsonResponse
from django.shortcuts import render

from web import models


def statistics(request):
    return render(request, 'statistics.html')


def statistics_attackip(request):
    """ 统计外部服务网攻击IP数量 """
    """
       info =
       2024-05-17：{
               date:1715529600000.0,
               device:{
                   1:0,
                   2:1,
                   3:0,
               }
           },
       2024-05-16：{
               date:1715616000000,
               device:{
                   1:0,
                   2:1,
                   3:0,
               }
           },
       }
       """
    start = request.GET.get('start')
    end = request.GET.get('end')

    start_datetime = datetime.strptime(start, '%Y-%m-%d')
    end_datetime = datetime.strptime(end, '%Y-%m-%d')
    delta = (end_datetime - start_datetime).days
    date_dict = OrderedDict()

    device_mapping = {1: '瑞数动态防御系统', 2: '天眼威胁监测与分析系统', 3: '深信服防火墙'}
    for i in range(0, delta):
        date = start_datetime + timedelta(days=i)
        date_dict[date.strftime("%Y-%m-%d")] = {
            'name': date.strftime("%Y-%m-%d"),
            'device': {key: 0 for key, value in device_mapping.items()}
        }

    botgate_attackip = models.BotgateAttackIp.objects.filter(create_datetime__gte=start,
                                                             create_datetime__lt=end). \
        extra(select={'ctime': "DATE_FORMAT(web_botgateattackip.create_datetime,'%%Y-%%m-%%d')"}).values(
        'ctime', 'src_ip').annotate(ct=Count('src_ip'))
    for item in botgate_attackip:
        date_dict[item['ctime']]['device'][1] += 1

    skyeye_attackip = models.SkyEyeLog.objects.filter(create_datetime__gte=start,
                                                      create_datetime__lt=end).exclude(attack_sip=''). \
        extra(select={'ctime': "DATE_FORMAT(web_skyeyelog.create_datetime,'%%Y-%%m-%%d')"}).values(
        'ctime', 'attack_sip').annotate(ct=Count('attack_sip'))

    for item in skyeye_attackip:
        date_dict[item['ctime']]['device'][2] += 1

    sangfor_fw_attackip = models.SangforFwAttackLog.objects.filter(create_datetime__gte=start,
                                                                   create_datetime__lt=end). \
        extra(select={'ctime': "DATE_FORMAT(web_sangforfwattacklog.create_datetime,'%%Y-%%m-%%d')"}). \
        values('ctime', 'src_ip').annotate(ct=Count('id'))

    for item in sangfor_fw_attackip:
        date_dict[item['ctime']]['device'][3] += 1

    categories = [data['name'] for data in date_dict.values()]

    data_result_dict = OrderedDict()
    for item in device_mapping.items():
        data_result_dict[item[0]] = {'name': item[1], "data": []}
    for key, text in device_mapping.items():
        for row in date_dict.values():
            count = row['device'][key]
            data_result_dict[key]['data'].append(count)
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }

    return JsonResponse(context)


def statistics_attacktype(request):
    """ 统计瑞数攻击类型 """
    start = request.GET.get('start')
    end = request.GET.get('end')

    attack_type = models.BotgateAttackType.objects.filter(create_datetime__gte=start,
                                                          create_datetime__lt=end).values('attack_type').annotate(
        ct=Sum('attack_count')).order_by('-ct')[:10]
    # 1.构造字典
    data_dict = OrderedDict()
    for item in attack_type:
        data_dict[item['attack_type']] = {
            'name': item['attack_type'],
            'attack_count': item['ct']
        }
    categories = [data['name'] for data in data_dict.values()]
    data_result_dict = OrderedDict()
    data = [row['attack_count'] for row in data_dict.values()]
    data_result_dict['attack_count'] = {'name': '攻击总数', 'data': data}
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }

    return JsonResponse(context)


def statistics_injectattacktype(request):
    """ 统计瑞数漏洞攻击类型 """
    start = request.GET.get('start')
    end = request.GET.get('end')

    attack_type = models.BotgateInjectAttackType.objects.filter(create_datetime__gte=start,
                                                                create_datetime__lt=end).values('attack_type').annotate(
        ct=Sum('attack_count')).order_by('-ct')[:10]
    # 1.构造字典
    data_dict = OrderedDict()
    for item in attack_type:
        data_dict[item['attack_type']] = {
            'name': item['attack_type'],
            'attack_count': item['ct']
        }
    categories = [data['name'] for data in data_dict.values()]
    data_result_dict = OrderedDict()
    data = [row['attack_count'] for row in data_dict.values()]
    data_result_dict['attack_count'] = {'name': '漏洞攻击总数', 'data': data}
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }

    return JsonResponse(context)


def statistics_warningtype(request):
    """ 统计瑞数告警类型 """

    # 找到所有的问题，根据优先级分组，每个优先级的问题数量
    start = request.GET.get('start')
    end = request.GET.get('end')

    warning_type = models.BotgateWarnLog.objects.filter(create_datetime__gte=start,
                                                        create_datetime__lt=end).values('warning').annotate(
        ct=Sum('warning_count')).order_by('-ct')[:10]
    # 1.构造字典
    data_dict = OrderedDict()
    for item in warning_type:
        data_dict[item['warning']] = {
            'name': item['warning'],
            'warning_count': item['ct']
        }
    categories = [data['name'] for data in data_dict.values()]
    data_result_dict = OrderedDict()
    data = [row['warning_count'] for row in data_dict.values()]
    data_result_dict['warning_count'] = {'name': '告警总数', 'data': data}
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }

    return JsonResponse(context)


def statistics_ndaytype(request):
    """ 统计瑞数Nday攻击类型 """

    # 找到所有的问题，根据优先级分组，每个优先级的问题数量
    start = request.GET.get('start')
    end = request.GET.get('end')
    nday_type = models.BotgateNdayType.objects.filter(create_datetime__gte=start,
                                                      create_datetime__lt=end).values('nday_name').annotate(
        ct=Sum('nday_attack_count')).order_by('-ct')[:10]
    # 1.构造字典
    data_dict = OrderedDict()
    for item in nday_type:
        data_dict[item['nday_name']] = {
            'name': item['nday_name'],
            'nday_attack_count': item['ct']
        }
    categories = [data['name'] for data in data_dict.values()]
    data_result_dict = OrderedDict()
    data = [row['nday_attack_count'] for row in data_dict.values()]
    data_result_dict['nday_attack_count'] = {'name': 'Nday名称', 'data': data}
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }

    return JsonResponse(context)


def statistics_bottype(request):
    """ 统计高级自动化工具 """

    # 找到所有的问题，根据优先级分组，每个优先级的问题数量
    start = request.GET.get('start')
    end = request.GET.get('end')
    nday_type = models.BotgateBotType.objects.filter(create_datetime__gte=start,
                                                     create_datetime__lt=end).values('bot_type').annotate(
        ct=Sum('bot_attack_count')).order_by('-ct')[:10]
    # 1.构造字典
    data_dict = OrderedDict()
    for item in nday_type:
        data_dict[item['bot_type']] = {
            'name': item['bot_type'],
            'bot_attack_count': item['ct']
        }
    categories = [data['name'] for data in data_dict.values()]
    data_result_dict = OrderedDict()
    data = [row['bot_attack_count'] for row in data_dict.values()]
    data_result_dict['bot_attack_count'] = {'name': '高级自动化工具', 'data': data}
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }

    return JsonResponse(context)


def statistics_location(request):
    """ 统计瑞数Nday攻击类型 """

    # 找到所有的问题，根据优先级分组，每个优先级的问题数量
    start = request.GET.get('start')
    end = request.GET.get('end')
    location = models.BotgateAttackIp.objects.filter(create_datetime__gte=start,
                                                     create_datetime__lt=end).values('location').annotate(
        ct=Sum('attack_count')).filter(ct__gt=0).order_by('-ct')[:20]
    # 1.构造字典
    data_dict = OrderedDict()
    for item in location:
        data_dict[item['location']] = {
            'name': item['location'],
            'location_attack_count': item['ct']
        }
    categories = [data['name'] for data in data_dict.values()]
    data_result_dict = OrderedDict()
    data = [row['location_attack_count'] for row in data_dict.values()]
    data_result_dict['location_attack_count'] = {'name': '攻击来源', 'data': data}
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }

    return JsonResponse(context)


def statistics_skyeye_warningtype(request):
    """ 天眼告警类型统计 """

    # 找到所有的问题，根据优先级分组，每个优先级的问题数量
    start = request.GET.get('start')
    end = request.GET.get('end')

    warning_type = models.SkyEyeLog.objects.filter(create_datetime__gte=start,
                                                   create_datetime__lt=end).values('type').annotate(
        ct=Count('attack_sip')).order_by('-ct')
    # super_type_dict = {item[0]: item[1] for item in models.SkyEyeLog.type_choices}
    # 1.构造字典
    data_dict = OrderedDict()
    for item in warning_type:
        data_dict[item['type']] = {
            'name': item['type'],
            'host_state': {item[0]: 0 for item in models.SkyEyeLog.host_state_choices}
        }
    queryset = models.SkyEyeLog.objects.filter(create_datetime__gte=start,
                                               create_datetime__lt=end)
    for item in queryset:
        data_count = data_dict[item.type]['host_state'][item.host_state]
        data_dict[item.type]['host_state'][item.host_state] = data_count + 1

    categories = [data['name'] for data in data_dict.values()]

    data_result_dict = OrderedDict()
    for item in models.SkyEyeLog.host_state_choices:
        data_result_dict[item[0]] = {'name': item[1], "data": []}

    for key, text in models.SkyEyeLog.host_state_choices:
        # key=1,text='新建'
        for row in data_dict.values():
            count = row['host_state'][key]
            data_result_dict[key]['data'].append(count)
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }
    return JsonResponse(context)


def statistics_sangforfw_attacktype(request):
    """ 深信服防火墙威胁类型统计 """

    # 找到所有的问题，根据优先级分组，每个优先级的问题数量
    start = request.GET.get('start')
    end = request.GET.get('end')
    warning_type = models.SangforFwAttackLog.objects.filter(create_datetime__gte=start,
                                                            create_datetime__lt=end).values('attack_type').annotate(
        ct=Count('src_ip')).order_by('-ct')[:10]
    # super_type_dict = {item[0]: item[1] for item in models.SkyEyeLog.type_choices}
    # 1.构造字典
    data_dict = OrderedDict()
    for item in warning_type:
        data_dict[item['attack_type']] = {
            'name': item['attack_type'],
            'level': {item[0]: 0 for item in models.SangforFwAttackLog.level_choices}
        }
    queryset = models.SangforFwAttackLog.objects.filter(create_datetime__gte=start,
                                                        create_datetime__lt=end)
    for item in queryset:
        try:
            data_count = data_dict[item.attack_type]['level'][item.level]
            data_dict[item.attack_type]['level'][item.level] = data_count + 1
        except Exception as ex:
            continue

    categories = [data['name'] for data in data_dict.values()]

    data_result_dict = OrderedDict()
    for item in models.SangforFwAttackLog.level_choices:
        data_result_dict[item[0]] = {'name': item[1], "data": []}

    for key, text in models.SangforFwAttackLog.level_choices:
        # key=1,text='新建'
        for row in data_dict.values():
            count = row['level'][key]
            data_result_dict[key]['data'].append(count)
    context = {
        'status': True,
        'data': {
            'categories': categories,
            'series': list(data_result_dict.values())
        }
    }
    return JsonResponse(context)


def statistics_account(request):
    statistics = {}
    start = request.GET.get('start')
    end = request.GET.get('end')

    start_datetime_str = '{} {}'.format(start, '15:00:00')
    end_datetime_str = '{} {}'.format(end, '15:00:00')
    start_datetime = datetime.strptime(start_datetime_str, "%Y-%m-%d %H:%M:%S") - timedelta(days=1)
    end_datetime = datetime.strptime(end_datetime_str, "%Y-%m-%d %H:%M:%S") - timedelta(days=1)
    statistics['start_date'] = start_datetime
    statistics['end_date'] = end_datetime
    k01_attackcount = models.K01.objects.filter(create_datetime__gte=start,
                                                create_datetime__lt=end).aggregate(total=Sum('atk_count'))['total']
    k01_attackcount = k01_attackcount if k01_attackcount else 0

    botgate_attackcount = models.Botgate.objects.filter(create_datetime__gte=start,
                                                        create_datetime__lt=end).aggregate(
        total=Sum('abnormal_request_count'))['total']
    botgate_attackcount = botgate_attackcount if botgate_attackcount else 0
    sangfor_fw_attackcount = models.SangforFw.objects.filter(create_datetime__gte=start,
                                                             create_datetime__lt=end).aggregate(
        total=Sum('attack_count'))['total']
    sangfor_fw_attackcount = sangfor_fw_attackcount if sangfor_fw_attackcount else 0
    skyeye_attackcount = models.SkyEye.objects.filter(create_datetime__gte=start,
                                                      create_datetime__lt=end).aggregate(
        total=Sum(F('cnt_fatal') + F('cnt_high') + F('cnt_mid') + F('cnt_low')))['total']
    skyeye_attackcount = skyeye_attackcount if skyeye_attackcount else 0
    statistics['k01_attackcount'] = k01_attackcount
    statistics['botgate_attackcount'] = botgate_attackcount
    statistics['sangfor_fw_attackcount'] = sangfor_fw_attackcount
    statistics['skyeye_attackcount'] = skyeye_attackcount
    statistics['botgate_highwarning_count'] = models.Botgate.objects.filter(create_datetime__gte=start,
                                                                            create_datetime__lt=end).aggregate(
        total=Sum('high_warning_count'))['total']
    statistics['botgate_middlewarning_count'] = models.Botgate.objects.filter(create_datetime__gte=start,
                                                                              create_datetime__lt=end).aggregate(
        total=Sum('middle_warning_count'))['total']
    statistics['botgate_lowwarning_count'] = models.Botgate.objects.filter(create_datetime__gte=start,
                                                                           create_datetime__lt=end).aggregate(
        total=Sum('low_warning_count'))['total']

    statistics['total_count'] = k01_attackcount + botgate_attackcount + sangfor_fw_attackcount + skyeye_attackcount
    botgate_website_account = models.BotgateWebSiteRequest.objects.filter(create_datetime__gte=start,
                                                                          create_datetime__lt=end,
                                                                          request_type=2).values('website'). \
                                  annotate(ct=Sum('request_count')).order_by('-ct')[0:5]
    statistics['botgate_website'] = ",".join(
        ["{},<span class='highlight-text'>{:,}</span>次".format(item['website'], item['ct']) for item in
         botgate_website_account])
    statistics['k01_attackip_count'] = models.K01AttackIp.objects.filter(create_datetime__gte=start,
                                                                         create_datetime__lt=end).values(
        'sip').annotate(ct=Count('sip')).count()
    botgate_attackip = models.BotgateAttackIp.objects.filter(create_datetime__gte=start,
                                                             create_datetime__lt=end).values('src_ip'). \
        annotate(ct=Count('src_ip'))
    botgate_attackip_set = {item['src_ip'] for item in botgate_attackip}
    sangfor_fw_attackip = models.SangforFwAttackLog.objects.filter(create_datetime__gte=start,
                                                                   create_datetime__lt=end).values('src_ip'). \
        annotate(ct=Count('src_ip'))
    sangfor_fw_attackip_set = {item['src_ip'] for item in sangfor_fw_attackip}
    skyeye_attackip = models.SkyEyeLog.objects.filter(create_datetime__gte=start,
                                                      create_datetime__lt=end).exclude(attack_sip='').values(
        'attack_sip').annotate(ct=Count('attack_sip'))
    skyeye_attackip_set = {item['attack_sip'] for item in skyeye_attackip}
    statistics['botgate_attackip_count'] = len(botgate_attackip_set)
    statistics['sangfor_fw_attackip_count'] = len(sangfor_fw_attackip_set)
    statistics['skyeye_attackip_count'] = len(skyeye_attackip_set)
    # statistics['attackip_totalcount'] = len(botgate_attackip_set | sangfor_fw_attackip_set | skyeye_attackip_set)
    statistics['attackip_totalcount'] = statistics['botgate_attackip_count'] + statistics['sangfor_fw_attackip_count'] + \
                                        statistics['skyeye_attackip_count']
    blockip_queryset = models.BlockIp.objects.filter(create_datetime__gte=start,
                                                     create_datetime__lt=end)
    blockip_list = blockip_queryset.values('src_ip', 'location').annotate(ct=Count('src_ip')).order_by('-ct')[:10]
    statistics['blockip_list'] = ",".join([
        "{}({}),攻击<span class='highlight-text'>{}</span>次".format(item['src_ip'], item['location'], item['ct']) for item
        in
        blockip_list])
    statistics['blockip_manual_count'] = blockip_queryset.filter(block_type=1).count()
    statistics['blockip_automatic_count'] = blockip_queryset.filter(block_type=2).count()
    location_account = models.BlockIp.objects.filter(create_datetime__gte=start,
                                                     create_datetime__lt=end).exclude(
        location__isnull=True).values('location').annotate(ct=Count('src_ip')).order_by('-ct')[0:10]

    statistics['botgate_location_account'] = ",".join(
        ["{}<span class='highlight-text'>{}</span>个".format(item.get('location'), item.get("ct")) for item in location_account])

    inject_attack_account = models.BotgateInjectAttackType.objects.filter(create_datetime__gte=start,
                                                                          create_datetime__lt=end).values(
        'attack_type').annotate(ct=Sum('attack_count')).order_by('-ct')[0:5]
    statistics['inject_attack_account'] = ",".join(
        ["{},<span class='highlight-text'>{:,}</span>次".format(item['attack_type'], item['ct']) for item in
         inject_attack_account])

    nday_type_account = models.BotgateNdayType.objects.filter(create_datetime__gte=start,
                                                              create_datetime__lt=end).values(
        'nday_name').annotate(ct=Sum('nday_attack_count')).order_by('-ct')[0:5]
    statistics['nday_type_account'] = ",".join(
        ["{},<span class='highlight-text'>{:,}</span>次".format(item['nday_name'], item['ct']) for item in
         nday_type_account])

    bot_type_account = models.BotgateBotType.objects.filter(create_datetime__gte=start,
                                                            create_datetime__lt=end).values(
        'bot_type').annotate(ct=Sum('bot_attack_count')).order_by('-ct')[0:5]
    statistics['bot_type_account'] = ",".join(
        ["{},<span class='highlight-text'>{:,}</span>次".format(item['bot_type'], item['ct']) for item in
         bot_type_account])

    content_data = """
    <p><span class="highlight-text">{start_date}至{end_date}</span>，外部服务网共计拦截攻击<span class="highlight-text">{total_count}</span>次，
    其中k01<span class="highlight-text">{k01_attackcount}</span>次;深信服防火墙<span class="highlight-text">{sangfor_fw_attackcount}</span>次;
    瑞数动态防御系统<span class="highlight-text">{botgate_attackcount}</span>次，高危告警<span class="highlight-text">{botgate_highwarning_count}</span>次，
    中危告警<span class="highlight-text">{botgate_middlewarning_count}</span>次，低危告警<span class="highlight-text">{botgate_lowwarning_count}</span>次,
    主要攻击站点为(Top5)：{botgate_website};
    威胁监测与分析系统（天眼）报警 <span class="highlight-text">{skyeye_attackcount}</span>次。</p>
    <p><span style='font-weight:bold;'>主要攻击类型（Top5):</span>{inject_attack_account}。
    其中Nday类型（Top5):{nday_type_account}。高级自动化工具(Top5):{bot_type_account}
    </p>
    <p><span style='font-weight:bold;'>恶意IP监控：</span>K01威胁情报库恶意IP<span class="highlight-text">{k01_attackip_count}</span>个，
    瑞数动态防御系统恶意IP<span class="highlight-text">{botgate_attackip_count}</span>个,
    深信服防火墙恶意IP<span class="highlight-text">{sangfor_fw_attackip_count}</span>个,
    威胁监测与分析系统（天眼）恶意IP<span class="highlight-text">{skyeye_attackip_count}</span>个,
    共计<span class="highlight-text">{attackip_totalcount}</span>个。
    集中分析平台自动封禁高危IP<span class="highlight-text">{blockip_automatic_count}</span>个，
    人工研判封禁高危IP<span class="highlight-text">{blockip_manual_count}</span>个。
    主要威胁来源：{botgate_location_account}。
    </p>
    <p><span style='font-weight:bold;'>恶意IP列表：</span>{blockip_list}。</p>
    """.format(start_date=statistics['start_date'],
               end_date=statistics['end_date'],
               k01_attackcount="{:,}".format(statistics['k01_attackcount']),
               sangfor_fw_attackcount="{:,}".format(statistics['sangfor_fw_attackcount']),
               botgate_attackcount="{:,}".format(statistics['botgate_attackcount']),
               skyeye_attackcount="{:,}".format(statistics['skyeye_attackcount']),
               botgate_highwarning_count="{:,}".format(statistics['botgate_highwarning_count']),
               botgate_middlewarning_count="{:,}".format(statistics['botgate_middlewarning_count']),
               botgate_lowwarning_count="{:,}".format(statistics['botgate_lowwarning_count']),
               total_count="{:,}".format(statistics['total_count']),
               botgate_website=statistics['botgate_website'],
               k01_attackip_count=statistics['k01_attackip_count'],
               botgate_attackip_count=statistics['botgate_attackip_count'],
               sangfor_fw_attackip_count=statistics['sangfor_fw_attackip_count'],
               skyeye_attackip_count=statistics['skyeye_attackip_count'],
               attackip_totalcount=statistics['attackip_totalcount'],
               blockip_automatic_count=statistics['blockip_automatic_count'],
               blockip_manual_count=statistics['blockip_manual_count'],
               botgate_location_account=statistics['botgate_location_account'],
               inject_attack_account=statistics['inject_attack_account'],
               nday_type_account=statistics['nday_type_account'],
               bot_type_account=statistics['bot_type_account'],
               blockip_list=statistics['blockip_list']
               )
    context = {
        'status': True,
        'data': content_data
    }
    return JsonResponse(context)
